﻿using System;
using System.Data;
using System.Collections;
using CE.Database;

/// <summary>
/// Summary description for ClaimDB
/// </summary>
namespace CE.Board
{
    public class BoardDB
    {
        public BoardDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 Insert(Board boardetail)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocBoardInsertUpdateSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CEOBoardId", boardetail.CEOBoardId);
            _sPara.Add("@Subject", boardetail.Subject);
            _sPara.Add("@Question", boardetail.Question);
            _sPara.Add("@Answer", boardetail.Answer);
            _sPara.Add("@Name", boardetail.Name);
            _sPara.Add("@Email", boardetail.Email);
            _sPara.Add("@Phone", boardetail.Phone);
            _sPara.Add("@IsPublished", boardetail.IsPublished);
            _sPara.Add("@IsAnswer", (boardetail.Answer.Trim() != ""));
            _sPara.Add("@QuestionBy", boardetail.QuestionBy);
            _sPara.Add("@AnswerBy", boardetail.AnswerBy);
            _sPara.Add("@SenderIP", boardetail.SenderIP);
            _sPara.Add("@QuestionDate", boardetail.QuestionDate);
            if (boardetail.AnswerDate != null)
            {
                _sPara.Add("@AnswerDate", boardetail.AnswerDate);
            }
            else
            {
                _sPara.Add("@AnswerDate", DBNull.Value);
            }
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public DataTable GetBoardPublished()
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "SELECT  * FROM tblCEOBoard WHERE IsPublished = '1' ORDER BY CEOBoardId DESC";

            DataTable _dtCarModel = _db.GetDataTable(strSQL);

            return _dtCarModel;
        }

        public DataTable GetBoard()
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "SELECT  * FROM tblCEOBoard ORDER BY CreatedDate";

            DataTable _dtCarModel = _db.GetDataTable(strSQL);

            return _dtCarModel;
        }

        public DataTable GetBoard(string Search, int IsPublish, string Name, string Phone, string Email)
        {
            //IsPublish
            //1 = Yes
            //0 = No
            //2 = All
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocBoardSearchList";

            SortedList _sPara = new SortedList();
            _sPara.Add("@Search", Search);
            _sPara.Add("@IsPublished", IsPublish);
            _sPara.Add("@Name", Name);
            _sPara.Add("@Phone", Phone);
            _sPara.Add("@Email", Email);

            DataTable _dtCarModel = _db.GetDataTableBySP(_spName, _sPara);

            return _dtCarModel;
        }

        public Int64 DeleteBoard(Int64 BoardId)
        {
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocBoardDeleteSingleItem";

            SortedList _sPara = new SortedList();
            _sPara.Add("@CEOBoardId", BoardId);

            return _db.ExecuteSP(_spName, _sPara);
        }

        public Board GetBoardSingleItem(int BoardId)
        {
            Board _board = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "SELECT * FROM tblCEOBoard WHERE CEOBoardId=" + BoardId.ToString();

            using (DataTable _dt = _db.GetDataTable(_spName))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _board = new Board();
                    _board = FillDataRecord(_dt);
                }
            }
            return _board;
        }

        private Board FillDataRecord(DataTable myDataTable)
        {
            Board myBoard = new Board();
            myBoard.CEOBoardId = Convert.ToInt32(myDataTable.Rows[0]["CEOBoardId"].ToString());
            myBoard.Subject = myDataTable.Rows[0]["Subject"].ToString();
            myBoard.Question = myDataTable.Rows[0]["Question"].ToString();
            myBoard.Answer = myDataTable.Rows[0]["Answer"].ToString();
            myBoard.Name = myDataTable.Rows[0]["Name"].ToString();
            myBoard.Phone = myDataTable.Rows[0]["Phone"].ToString();
            myBoard.Email = myDataTable.Rows[0]["Email"].ToString();
            myBoard.IsPublished = Convert.ToBoolean(myDataTable.Rows[0]["IsPublished"].ToString());
            myBoard.QuestionBy = Convert.ToInt64(myDataTable.Rows[0]["QuestionBy"].ToString());
            myBoard.AnswerBy = Convert.ToInt64(myDataTable.Rows[0]["AnswerBy"].ToString());
            myBoard.SenderIP = myDataTable.Rows[0]["SenderIP"].ToString();
            myBoard.QuestionDate = Convert.ToDateTime(myDataTable.Rows[0]["QuestionDate"].ToString());
            return myBoard;
        }

    }
}
